package jsyf.com.util;


import com.alibaba.fastjson.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @author: 徐波
 * @creat: 2017/11/22 0022 10:07
 * description:
 * 导入导出excel表格
 */
public class ExcelUtil {

    /**
     * @param args
     * @Description: 测试方法  可以到本地  <br/>
     * @return: void
     * @throw:
     * @author: 徐波
     * @createTime: 2017/11/22 0022 13:32
     * @Version: V1.0.0
     */
    public static void main(String args[]) {
        try {
          //  ExcelToList("/Users/GUOQI/Java/workplace_svn/p5_server/shop/jsyf-oa/src/main/java/jsyf/oa/web/migration/res/sys_user.xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * @param list
     * @Description: 导出excel表格 返回HSSFWorkbook 利用输出流可以导出到页面中下载或者本地 为2003及之前的版本<br/>
     * 在controller层 使用返回的wb对象，写入response的输出流中
     * wb.write(response.getOutputStream());
     * 再响应给前端：ResponseUtils.putFileResponse(request,response,fileName+".xls");
     * @return: org.apache.poi.hssf.usermodel.HSSFWorkbook
     * @throw:
     * @author: 徐波
     * @createTime: 2017/11/22 0022 13:24
     * @Version: V1.0.0
     */
    public static HSSFWorkbook ExportExcel(List<JSONObject> list) throws Exception {

        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();

        //创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("sheet0");

        //创建HSSFRow对象 行
        HSSFRow row = sheet.createRow(0);

        //设置列名
        String str = list.get(0).toString().substring(1, list.get(0).toString().length() - 1);

        String[] strs = str.split(",");

        for (int i = 0; i < strs.length; i++) {
            HSSFCell cell = row.createCell(i);
            String cellName = strs[i].split(":")[0].replaceAll("\"", "");
            cell.setCellValue(cellName);
        }

        //设置每行的列值
        for (int i = 0; i < list.size(); i++) {

            //从第二行开始
            HSSFRow sonRow = sheet.createRow(i + 1);

            JSONObject sonCellObj = list.get(i);

            //设置单元格的值
            for (int j = 0; j < sonCellObj.size(); j++) {

                HSSFCell sonCell = sonRow.createCell(j);

                String cellValue = sonCellObj.getString(row.getCell(j).toString());

                if (StringUtils.isNotBlank(cellValue)) {
                    sonCell.setCellValue(cellValue);
                } else {
                    sonCell.setCellValue(" ");
                }
            }

        }
        //输出Excel文件到本地
//        FileOutputStream output= new FileOutputStream("F:\\workbook.xls");
//        wb.write(output);
//        output.flush();
        return wb;
    }


    /**
     * @param xlsPath
     * @Description: excel文件转成list  <br/>
     * 文件要求为第一行为字段名，不可为空，表中数据不能隔行，数据可以为空
     * @return: java.util.List<com.alibaba.fastjson.JSONObject>
     * @throw:
     * @author: 徐波
     * @createTime: 2017/11/22 0022 14:59
     * @Version: V1.0.0
     */
  /*  public static List<JSONObject> ExcelToList(String xlsPath) throws Exception {

        List<JSONObject> temp = new ArrayList<>();
        FileInputStream fileIn = new FileInputStream(new File(xlsPath));

        //根据指定的文件输入流导入Excel从而产生Workbook对象
        XSSFWorkbook wb = new XSSFWorkbook(fileIn);


        //获取Excel文档中的第一个表单
        XSSFSheet sheet = wb.getSheetAt(0);

        //获取第一行名
        XSSFRow row = sheet.getRow(0);

        //迭代第一行
        Iterator rowOne = row.iterator();

        List<String> rowNameList = new ArrayList<>();
        //获取行名  及字段名
        while (rowOne.hasNext()) {
            String rowName = rowOne.next().toString();
            rowNameList.add(rowName);
        }

        //循环获取值并加入list
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            JSONObject rowObj = new JSONObject();
            XSSFRow sonRow = sheet.getRow(i + 1);

            for (int j = 0; j < rowNameList.size(); j++) {
                System.out.println(i + "行" + j + "列");
                if (sonRow.getCell(j).toString().contains("E1") && sonRow.getCell(j).toString().contains(".")) {
                    //修改电话号码带E问题
                    DecimalFormat df = new DecimalFormat("#");
                    rowObj.put(rowNameList.get(j), df.format(Double.parseDouble(sonRow.getCell(j).toString())));
                } else if (sonRow.getCell(j).toString().contains(".0")) {
                    rowObj.put(rowNameList.get(j), sonRow.getCell(j).toString().replace(".0",""));
                } else {
                    rowObj.put(rowNameList.get(j), StringUtils.isBlank(sonRow.getCell(j).toString()) ? "0" : sonRow.getCell(j).toString());
                }

            }
            temp.add(rowObj);
        }
        System.out.println(temp.toString());
        fileIn.close();
        return temp;
    }
*/

}
